package com.cls.business.mapper;

import com.cls.business.entity.ApplicantInfo;
import com.cls.business.entity.DeclareTopic;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.cls.business.entity.Topic;
import com.cls.business.entity.TopicSubjectDetail;
import com.cls.business.vo.*;
import com.cls.business.vo.preitem.report.PreItemExport;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Map;

/**
 * 申报课题表 Mapper
 *
 * @author weimaomao
 * @date 2020-11-10 11:09:57
 */
@Repository
public interface DeclareTopicMapper extends BaseMapper<DeclareTopic> {

    /**
     * 魏起成查询定评评审列表包含计划数据
     *
     * @param declareTopic
     * @return
     */
    List<DeclareTopicVO> findDeclareTopicsAndTopicPlan(@Param("declareTopic") DeclareTopic declareTopic);

    @Select("select b.plan_topic_id as topic_id, count(0) as num , a.subject_type_code,b.plan_id from  biz_declare_plan b " +
            "Left join biz_declare_topic a on a.declare_id = b.declare_id " +
            "where b.plan_topic_id = #{topicId} and a.status_type_code = #{statusTypeCode} " +
            "and b.is_delete = false group by b.plan_topic_id, a.subject_type_code order by num desc")
    List<TopicSubjectDetail> countDeclareTopicsBySubjectType(@Param("topicId") Long topicId, @Param("statusTypeCode") String statusTypeCode);

    /**
     * 魏起成
     * 根据状态查询年度课题并根据年份分组
     *
     * @param statusTypeCode
     * @return
     */
    @Select(" select topic.* from biz_topic topic,biz_declare_topic decl " +
            " where  " +
            " topic.topic_id=decl.topic_id  " +
            " and  " +
            " topic.is_delete=0  " +
            " and  " +
            " decl.is_delete=0 " +
            " and " +
            " decl.status_type_code=#{statusTypeCode} " +
            " and  decl.topic_classify_code=#{topicClassifyCode} " +
            " GROUP BY year ")
    List<Topic> selectByStatusGroupByYear(@Param("statusTypeCode") String statusTypeCode, @Param("topicClassifyCode") String topicClassifyCode);

    /**
     * 魏起成
     * 根据年份查询此年份所有的拟立项审查通过的数据
     *
     * @param year
     * @param statusTypeCode
     */
    @Select("select decl.*,dict.value as finalLevelTypeVal from biz_topic topic,biz_declare_topic decl,sys_dict dict\n" +
            "where\n" +
            "topic.topic_id=decl.topic_id\n" +
            "and\n" +
            "dict.code=decl.final_level_type_code \n" +
            "and\n" +
            "topic.is_delete=0\n" +
            "and\n" +
            "decl.is_delete=0\n" +
            "and \n" +
            "year = #{year}\n" +
            "and decl.topic_classify_code=#{topicClassifyCode} and " +
            "decl.status_type_code=#{statusTypeCode}")
    List<DeclareTopic> selectByStatusAndYear(@Param("statusTypeCode") String statusTypeCode, @Param("year") Integer year, @Param("topicClassifyCode") String topicClassifyCode);


    @Select("select a.* from biz_declare_plan b " +
            "LEFT JOIN  biz_declare_topic a on b.declare_id = a.declare_id " +
            "where a.status_type_code = #{statusTypeCode} and  b.plan_topic_id = #{topicId} and b.plan_id = #{planId} and a.is_delete = false")
    List<DeclareTopic> selectByStatusAndTopicId(@Param("statusTypeCode") String statusTypeCode, @Param("topicId") Long topicId, @Param("planId") Long planId);


    @Select("select a.* from biz_declare_plan b " +
            "LEFT JOIN  biz_declare_topic a on b.declare_id = a.declare_id " +
            "where b.plan_topic_id = #{topicId} and b.plan_id = #{planId} and a.is_delete = false")
    List<DeclareTopic> selectByTopicIdAndPlanId(@Param("topicId") Long topicId, @Param("planId") Long planId);


    @Select("select a.* from biz_declare_plan b " +
            "LEFT JOIN  biz_declare_topic a on b.declare_id = a.declare_id " +
            "where b.plan_id = #{planId} and a.is_delete = false")
    List<DeclareTopic> selectByPlanId(@Param("planId") Long planId);

    /**
     * 根据计划id、状态类型查询
     *
     * @param planId
     * @param statusTypeCodes
     * @return
     */
    List<DeclareTopic> selectByPlanIdAndStatusTypeCode(@Param("planId") Long planId, @Param("statusTypeCodes") List<String> statusTypeCodes);


    /**
     * 查询申请人的所有信息用于导出word
     *
     * @param userId
     * @return
     */
    DeclareTopicPreItemApplyExport selectApplicantAndDeclareByUserIdAndDeclareId(@Param("userId") Long userId, @Param("declareId") Long declareId);


    /**
     * 查询委托的所有信息用于导出word
     *
     * @param declareId
     * @return
     */
    DeclareEntrustPreItemApplyExport exportEntrustPreItemApply(@Param("declareId") Long declareId);


    List<SelectEntrustVO> chooseEntrusts(Map<String, Object> param);

    List<SelectEntrustVO> assignEntrusts(Long planId);

    List<DeclareTopic> applyCapitalDeclareTopics();


    @Select("SELECT decl.*,app.user_id,dict.name final_level_type_val FROM biz_declare_topic decl " +
            " inner join biz_applicant_info app on app.applicant_id=decl.applicant_id " +
            " inner join biz_declare_plan plan on plan.declare_id=decl.declare_id " +
            " left join sys_dict dict on dict.`code`=decl.final_level_type_code " +
            " WHERE plan.plan_id=#{planId} and decl.status_type_code=#{statusTypeCode} and decl.is_delete=0 and decl.topic_classify_code=#{topicClassifyCode} ")
    List<DeclareTopic> findAnnualDeclareTopicByPlanIdAndStatusTypeCode(@Param("planId") Long planId, @Param("statusTypeCode") String statusTypeCode, @Param("topicClassifyCode") String topicClassifyCode);


    @Select("SELECT decl.*,app.user_id FROM biz_declare_topic decl inner join biz_applicant_info app on app.applicant_id=decl.applicant_id \n" +
            " where decl.is_delete=0 and decl.declare_id=#{declareTopicId}")
    ApplicantInfo findApplicantInfoById(Long declareTopicId);

    @Select("SELECT decl.*,app.user_id,dict.`name` final_level_type FROM biz_declare_topic decl \n" +
            "inner join biz_applicant_info app on app.applicant_id=decl.applicant_id\n" +
            "inner join biz_declare_plan plan on plan.declare_id=decl.declare_id\n" +
            "inner join sys_dict dict on dict.`code`= decl.final_level_type_code\n" +
            "where plan.plan_id=#{planId} and decl.is_delete=0 ")
    List<DeclareTopic> findFinalLevelTypeAndUserIdByPlanId(Long planId);


    /**
     * 根据期次进行查询所有的申请课题
     * @param period
     * @return
     */
  /*  @Select("select decl.* from biz_declare_topic decl  \n" +
            "inner join biz_declare_plan declarePlan on declarePlan.plan_id=(select plan_id from biz_topic_plan where period=#{period} and is_delete=0)\n" +
            "where decl.is_delete=0")
    List<DeclareTopic> selectByPeriod(String period);*/

    /**
     * 导出拟立项的查询sql
     *
     * @param declareTopic
     * @return
     */
    List<PreItemExport> exportPreItem(@Param("declareTopic") DeclareTopic declareTopic);

    /**
     * 根据期次查询第一次未通过的所有课题
     * @param period
     * @return
     */
    @Select("select * from biz_declare_topic decl\n" +
            "inner join biz_declare_plan declare_plan on declare_plan.declare_id=decl.declare_id\n" +
            "inner join biz_topic_plan plan on plan.plan_id=declare_plan.plan_id\n" +
            "where decl.is_delete=0 and decl.apply_count=1 and decl.latest_apply_time is null and decl.status_type_code='conclude_failed'\n" +
            "and plan.period=#{period}")
    List<DeclareTopic> selectFirstNoPass(@Param("period")String period);

}
